﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using blackhouse.Data;
using System.Data.SqlClient;
using System.Data;

namespace blackhouse.BlackApart.Data.Sql {
    public class ReservationDataAccess : AdoReservationDataAccess {

        #region Fields

        private string connectionString;
        private MsSqlAdoDataHelper dataHelper;

        #endregion

        #region Constructors

        public ReservationDataAccess(string sqlConnectionString) {
            this.connectionString = sqlConnectionString;
            this.dataHelper = new MsSqlAdoDataHelper(this.connectionString);
            SqlVersioning versioning = new SqlVersioning(this.connectionString, this.GetType().Assembly);
        }

        #endregion

        #region CalendarDataAccess Members

        //public override bool RemoveReservation(int reservationId) {
        //    this.dataHelper.Reset();
        //    this.dataHelper.ProcedureName = "bhp_ApartmentReservationRemove";
        //    return this.dataHelper.ExecuteWithoutResult() > 0;
        //}
        //public override int SaveReservation(int reservationId, int apartmentId, double price, double priceWithoutRabat, float rabat, int numberOfPeople, DateTime startDate, DateTime endDate) {
        //    this.dataHelper.Reset();
        //    this.dataHelper.ProcedureName = "bhp_ApartmentReservationSave";
        //    this.dataHelper.AddParameter("Id", reservationId, System.Data.DbType.Int32);
        //    this.dataHelper.AddParameter("ApartmentId", apartmentId, System.Data.DbType.Int32);
        //    this.dataHelper.AddParameter("Price", price, System.Data.DbType.Double);
        //    this.dataHelper.AddParameter("PriceWithoutRabat", priceWithoutRabat, System.Data.DbType.Double);
        //    this.dataHelper.AddParameter("Rabat", rabat, System.Data.DbType.Single);
        //    this.dataHelper.AddParameter("NumberOfPeople", numberOfPeople, System.Data.DbType.Int32);
        //    this.dataHelper.AddParameter("StartDate", startDate, System.Data.DbType.DateTime);
        //    this.dataHelper.AddParameter("EndDate", endDate, System.Data.DbType.DateTime);
        //    try {
        //        return (int)this.dataHelper.ExecuteScalar();
        //    }
        //    catch (SqlException e) {
        //        if (e.State == 123)
        //            throw new ExceptionData(1, e.Message, e);
        //        else throw;

        //    }
        //}

        //protected override IDataReader GetReaderForReservations(int apartmentId, DateTime startDate, DateTime endDate) {
        //    this.dataHelper.Reset();
        //    this.dataHelper.ProcedureName = "bhp_ApartmentReservationGetByApartment";
        //    this.dataHelper.AddParameter("ApartmentId", apartmentId, System.Data.DbType.Int32);
        //    this.dataHelper.AddParameter("StartDate", startDate, System.Data.DbType.DateTime);
        //    this.dataHelper.AddParameter("EndDate", endDate, System.Data.DbType.DateTime);
        //    return this.dataHelper.Execute();
        //}

        protected override IDataReader GetReaderForClient(int clientId) {
            this.dataHelper.Reset();
            this.dataHelper.ProcedureName = "bhp_ClientGet";
            this.dataHelper.AddParameter("ClientId", clientId, System.Data.DbType.Int32);
            return this.dataHelper.Execute();
        }

        protected override IDataReader GetReaderForClientByEmail(string email) {
            this.dataHelper.Reset();
            this.dataHelper.ProcedureName = "bhp_ClientGetByEmail";
            this.dataHelper.AddParameter("Email", email, System.Data.DbType.String);
            return this.dataHelper.Execute();
        }

        public override int SaveClient(string eMail, string phoneNumber, string personTitle, string firstName, string lastName, string language, string userPrefferCurrency) {
            this.dataHelper.Reset();
            this.dataHelper.ProcedureName = "bhp_ClientSave";
            this.dataHelper.AddParameter("EMail", eMail, DbType.String);
            this.dataHelper.AddParameter("PhoneNumber", phoneNumber, DbType.String);
            this.dataHelper.AddParameter("PersonTitle", personTitle, DbType.String);
            this.dataHelper.AddParameter("FirstName", firstName, DbType.String);
            this.dataHelper.AddParameter("LastName", lastName, DbType.String);
            this.dataHelper.AddParameter("Language", language, DbType.StringFixedLength, 5);
            this.dataHelper.AddParameter("Currency", userPrefferCurrency, DbType.StringFixedLength, 3);
            try {
                return Convert.ToInt32(this.dataHelper.ExecuteScalar());
            }
            catch (SqlException e) {
                if (e.State == 123)
                    throw new ExceptionData(1, e.Message, e);
                else throw;
            }
        }

        //public override int SavePaymentType(int id, string key, bool waitForCash) {
        //    this.dataHelper.Reset();
        //    this.dataHelper.ProcedureName = "bhp_PaymentTypeSave";
        //    this.dataHelper.AddParameter("Id", id, DbType.Int32);
        //    this.dataHelper.AddParameter("Key", key, DbType.String);
        //    this.dataHelper.AddParameter("WaitForCash", waitForCash, DbType.Boolean);
        //    return this.dataHelper.ExecuteScalar<int>();
        //}

        //public override int AddReservationHeader(int clientId, double totalPrice, double totalPriceWithoutRabat, float rabat, bool vatInvoice, int paymentTypeId, string reservationTypeId) {
        //    this.dataHelper.Reset();
        //    this.dataHelper.ProcedureName = "bhp_ApartmentReservationHeaderAdd";
        //    this.dataHelper.AddParameter("ClientId", clientId, DbType.String);
        //    this.dataHelper.AddParameter("TotalPrice", totalPrice, DbType.Double);
        //    this.dataHelper.AddParameter("TotalPriceWithoutRabat", totalPriceWithoutRabat, DbType.Double);
        //    this.dataHelper.AddParameter("Rabat", rabat, DbType.Single);
        //    this.dataHelper.AddParameter("VatInvoice", vatInvoice, DbType.Boolean);
        //    this.dataHelper.AddParameter("PaymentTypeId", paymentTypeId, DbType.Int32);
        //    this.dataHelper.AddParameter("ReservationTypeId", reservationTypeId, DbType.String);
        //    return this.dataHelper.ExecuteScalar<int>();
        //}

        //public override bool JoinReservationHeader(int userReservationId, int apartmentReservationId) {
        //    this.dataHelper.Reset();
        //    this.dataHelper.ProcedureName = "bhp_ApartmentReservationHeaderJoin";
        //    this.dataHelper.AddParameter("UserReservationId", userReservationId, DbType.Int32);
        //    this.dataHelper.AddParameter("ReservationId", apartmentReservationId, DbType.Int32);
        //    return this.dataHelper.ExecuteWithoutResult() > 0;
        //}

        protected override IDataReader GetReaderForPaymentType(int paymentTypeId, string language) {
            this.dataHelper.Reset();
            this.dataHelper.ProcedureName = "bhp_PaymentTypeGet";
            this.dataHelper.AddParameter("Id", paymentTypeId, DbType.Int32);
            this.dataHelper.AddParameter("Language", language, DbType.StringFixedLength, 5);
            return this.dataHelper.Execute();
        }

        protected override IDataReader GetReaderForWebPaymentTypes(string language) {
            this.dataHelper.Reset();
            this.dataHelper.ProcedureName = "bhp_PaymentTypeGetAllWeb";
            this.dataHelper.AddParameter("Language", language, DbType.StringFixedLength, 5);
            return this.dataHelper.Execute();
        }

        //protected override IDataReader GetReaderForReservationHeader(int reservationId, string language) {
        //    this.dataHelper.Reset();
        //    this.dataHelper.ProcedureName = "bhp_ApartmentReservationHeaderGet";
        //    this.dataHelper.AddParameter("ReservationId", reservationId, DbType.Int32);
        //    this.dataHelper.AddParameter("Language", language, DbType.StringFixedLength, 5);
        //    return this.dataHelper.Execute();
        //}

        //protected override IDataReader GetReaderForReservationHeader(string reservationNumber, string language) {
        //    this.dataHelper.Reset();
        //    this.dataHelper.ProcedureName = "bhp_ApartmentReservationHeaderGet";
        //    this.dataHelper.AddParameter("ReservationNumber", reservationNumber, DbType.String);
        //    this.dataHelper.AddParameter("Language", language, DbType.StringFixedLength, 5);
        //    return this.dataHelper.Execute();
        //}

        //protected override IDataReader GetReaderForReservations(int reservationId) {
        //    this.dataHelper.Reset();
        //    this.dataHelper.ProcedureName = "bhp_ApartmentReservationGetByHeaderId";
        //    this.dataHelper.AddParameter("ReservationId", reservationId, DbType.Int32);
        //    return this.dataHelper.Execute();
        //}

        //public override void AddMailToSend(int reservationId) {
        //    this.dataHelper.Reset();
        //    this.dataHelper.ProcedureName = "bhp_ApartmentReservationSendMail";
        //    this.dataHelper.AddParameter("ReservationId", reservationId, DbType.Int32);
        //    this.dataHelper.ExecuteWithoutResult();
        //}

        public override bool ChangeReservationStatus(int reservationId, string newStatusKey) {
            this.dataHelper.Reset();
            this.dataHelper.ProcedureName = "bhp_WebReservationChangeStatus";
            this.dataHelper.AddParameter("ReservationId", reservationId, DbType.Int32);
            this.dataHelper.AddParameter("NewStatus", newStatusKey, DbType.String);
            return Convert.ToBoolean(this.dataHelper.ExecuteScalar());
        }

        protected override IDataReader AddWebReservationAndGetInfo(DateTime arrivalDate, DateTime departureDate, double priceWithRabat, double price, float rabat) {
            this.dataHelper.Reset();
            this.dataHelper.ProcedureName = "bhp_WebReservationAdd";
            this.dataHelper.AddParameter("ArrivalDate", arrivalDate, DbType.DateTime);
            this.dataHelper.AddParameter("DepartureDate", departureDate, DbType.DateTime);
            this.dataHelper.AddParameter("PriceWithRabat", priceWithRabat, DbType.Double);
            this.dataHelper.AddParameter("Price", price, DbType.Double);
            this.dataHelper.AddParameter("Rabat", rabat, DbType.Single);
            return this.dataHelper.Execute();
        }

        public override bool UpdateWebReservation(int reservationId, DateTime arrivalDate, DateTime departureDate, double finalPrice, double price, float rabat, bool vatInvoice, string comments, int clientId, int paymentTypeId) {
            this.dataHelper.Reset();
            this.dataHelper.ProcedureName = "bhp_WebReservationUpdate";
            this.dataHelper.AddParameter("ReservationId", reservationId, DbType.Int32);
            this.dataHelper.AddParameter("ArrivalDate", arrivalDate, DbType.DateTime);
            this.dataHelper.AddParameter("DepartureDate", departureDate, DbType.DateTime);
            this.dataHelper.AddParameter("PriceWithRabat", finalPrice, DbType.Double);
            this.dataHelper.AddParameter("Price", price, DbType.Double);
            this.dataHelper.AddParameter("Rabat", rabat, DbType.Single);
            this.dataHelper.AddParameter("VatInvoice", vatInvoice, DbType.Boolean);
            this.dataHelper.AddParameter("Comments", comments, DbType.String);
            this.dataHelper.AddParameter("ClientId", clientId, DbType.Int32);
            this.dataHelper.AddParameter("PaymentTypeId", paymentTypeId, DbType.Int32);
            return this.dataHelper.ExecuteWithoutResult() > 0;
        }

        public override int AddApartmentToReservation(int reservationId, int apartmentId, int forPeoples) {
            this.dataHelper.Reset();
            this.dataHelper.ProcedureName = "bhp_WebReservationAddApartment";
            this.dataHelper.AddParameter("ReservationId", reservationId, DbType.Int32);
            this.dataHelper.AddParameter("ApartmentId", apartmentId, DbType.Int32);
            this.dataHelper.AddParameter("Peoples", forPeoples, DbType.Int32);
            return Convert.ToInt32(this.dataHelper.ExecuteScalar());
        }

        public override int AddRabatToReservation(int reservationId, string rabatKey, float rabatValue) {
            this.dataHelper.Reset();
            this.dataHelper.ProcedureName = "bhp_WebReservationAddRabat";
            this.dataHelper.AddParameter("ReservationId", reservationId, DbType.Int32);
            this.dataHelper.AddParameter("RabatKey", rabatKey, DbType.String);
            this.dataHelper.AddParameter("RabatValue", rabatValue, DbType.Single);
            return Convert.ToInt32(this.dataHelper.ExecuteScalar());
        }

        public override void RollbackWebReservation(int reservationId) {
            this.dataHelper.Reset();
            this.dataHelper.ProcedureName = "[bhp_WebReservationDeleteWithRelations]";
            this.dataHelper.AddParameter("ReservationId", reservationId, DbType.Int32);
            this.dataHelper.ExecuteWithoutResult();
        }

        protected override IDataReader GetWebReservationReader(int reservationId, string language) {
            this.dataHelper.Reset();
            this.dataHelper.ProcedureName = "bhp_WebReservationGet";
            this.dataHelper.AddParameter("ReservationId", reservationId, DbType.Int32);
            this.dataHelper.AddParameter("Language", language, DbType.StringFixedLength);
            return this.dataHelper.Execute();
        }

        protected override IDataReader GetWebReservationReader(Guid reservationGuid, string language) {
            this.dataHelper.Reset();
            this.dataHelper.ProcedureName = "bhp_WebReservationGetByGuid";
            this.dataHelper.AddParameter("ReservationGuid", reservationGuid, DbType.Guid);
            this.dataHelper.AddParameter("Language", language, DbType.StringFixedLength);
            return this.dataHelper.Execute();
        }

        protected override IDataReader GetWebReservationReader(string reservationNumber, string language) {
            this.dataHelper.Reset();
            this.dataHelper.ProcedureName = "bhp_WebReservationGetByNumber";
            this.dataHelper.AddParameter("ReservationNumber", reservationNumber, DbType.String);
            this.dataHelper.AddParameter("Language", language, DbType.StringFixedLength);
            return this.dataHelper.Execute();
        }

        protected override IDataReader GetWebReservationForInterviewerReader() {
            this.dataHelper.Reset();
            this.dataHelper.ProcedureName = "bhp_WebReservationGetForOpinions";
            return this.dataHelper.Execute();
        }

        protected override IDataReader GetApartmentsWebReservationReader(int reservationId) {
            this.dataHelper.Reset();
            this.dataHelper.ProcedureName = "bhp_WebReservationGetApartments";
            this.dataHelper.AddParameter("ReservationId", reservationId, DbType.Int32);
            return this.dataHelper.Execute();
        }

        protected override IDataReader GetRabatsWebReservationReader(int reservationId, string language) {
            this.dataHelper.Reset();
            this.dataHelper.ProcedureName = "bhp_WebReservationGetRabats";
            this.dataHelper.AddParameter("ReservationId", reservationId, DbType.Int32);
            this.dataHelper.AddParameter("Language", language, DbType.StringFixedLength);
            return this.dataHelper.Execute();
        }

        protected override IDataReader GetAvabilityOfApartmentTypeReader(int groupId, DateTime fromDay, DateTime toDay) {
            this.dataHelper.Reset();
            this.dataHelper.ProcedureName = "bhp_WebReservationState";
            this.dataHelper.AddParameter("FromDate", fromDay, DbType.DateTime);
            this.dataHelper.AddParameter("ToDate", toDay, DbType.DateTime);
            this.dataHelper.AddParameter("GroupId", groupId, DbType.Int32);
            return this.dataHelper.Execute();
        }

        #endregion

    }
}
